package ar.com.larctest.providers;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import ar.com.larctest.dbHelper.SQLLARCTESTHelper;
import ar.com.larctest.entities.Measurement;
import ar.com.larctest.entities.MeasurementItem;
import ar.com.larctest.entities.temp.TMeasurement;
import ar.com.larctest.entities.temp.TMeasurementItem;
import ar.com.larctest.entities.temp.TMeasurementJoin;
import ar.com.larctest.providers.temp.MeasurementTempProvider;
import ar.com.larctest.utils.AppGlobalVariables;
import ar.com.larctest.utils.Constants;

public class MeasurementProvider {
    private static final String TAG = "MeasurementProvider";

    private static final String DB_MEASUREMENT_COLUMN_0_NAME = "idMeasurement";
    private static final String DB_MEASUREMENT_COLUMN_1_NAME = "dateMeasurement";
    private static final String DB_MEASUREMENT_COLUMN_2_NAME = "idTrack";
    private static final String DB_MEASUREMENT_COLUMN_3_NAME = "idTemperature";
    private static final String DB_MEASUREMENT_COLUMN_4_NAME = "idHumidity";
    private static final String DB_MEASUREMENT_COLUMN_5_NAME = "idUser";

    private static final String DB_MEASUREMENT_ITEM_COLUMN_0_NAME = "idMeasurementItem";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_1_NAME = "idMeasurement";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_2_NAME = "idHorse";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_3_NAME = "initTime";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_4_NAME = "finishTime";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_5_NAME = "lacticAcid1";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_6_NAME = "heartRate1";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_7_NAME = "lacticAcid2";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_8_NAME = "heartRate2";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_9_NAME = "lacticAcid3";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_10_NAME = "heartRate3";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_11_NAME = "respiratoryRate3";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_12_NAME = "idWork";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_13_NAME = "meters";
    private static final String DB_MEASUREMENT_ITEM_COLUMN_14_NAME = "time";

    private static final String DB_MEASUREMENT_COLUMN_HORSE_NAME = "name";

    private SQLiteDatabase database;
    private SQLLARCTESTHelper dbHelper;
    private Context context;
    private String[] allColumnsMeasurement = {DB_MEASUREMENT_COLUMN_0_NAME, DB_MEASUREMENT_COLUMN_1_NAME, DB_MEASUREMENT_COLUMN_2_NAME, DB_MEASUREMENT_COLUMN_3_NAME, DB_MEASUREMENT_COLUMN_4_NAME, DB_MEASUREMENT_COLUMN_5_NAME};
    private String[] allColumnsMeasurementItems = {DB_MEASUREMENT_ITEM_COLUMN_0_NAME, DB_MEASUREMENT_ITEM_COLUMN_1_NAME, DB_MEASUREMENT_ITEM_COLUMN_2_NAME, DB_MEASUREMENT_ITEM_COLUMN_3_NAME, DB_MEASUREMENT_ITEM_COLUMN_4_NAME, DB_MEASUREMENT_ITEM_COLUMN_5_NAME, DB_MEASUREMENT_ITEM_COLUMN_6_NAME, DB_MEASUREMENT_ITEM_COLUMN_7_NAME, DB_MEASUREMENT_ITEM_COLUMN_8_NAME, DB_MEASUREMENT_ITEM_COLUMN_9_NAME, DB_MEASUREMENT_ITEM_COLUMN_10_NAME, DB_MEASUREMENT_ITEM_COLUMN_11_NAME, DB_MEASUREMENT_ITEM_COLUMN_12_NAME, DB_MEASUREMENT_ITEM_COLUMN_13_NAME, DB_MEASUREMENT_ITEM_COLUMN_14_NAME};
    private String[] allJoinColumnsMeasurement = {DB_MEASUREMENT_ITEM_COLUMN_1_NAME, DB_MEASUREMENT_ITEM_COLUMN_0_NAME, DB_MEASUREMENT_COLUMN_HORSE_NAME, DB_MEASUREMENT_COLUMN_1_NAME};

    public MeasurementProvider(Context context) {
        dbHelper = SQLLARCTESTHelper.getHelper(context);
        database = dbHelper.getWritableDatabase();
        this.context = context;
    }

    public long insertMeasurement(Measurement measurement)
    {
        ContentValues contentValues;
        long nIdRecord;

        SimpleDateFormat sdf = new SimpleDateFormat(Constants.FORMAT_DATETIME);

        contentValues = new ContentValues();

        contentValues.put(DB_MEASUREMENT_COLUMN_1_NAME, sdf.format(measurement.getDateMeasurement()));
        contentValues.put(DB_MEASUREMENT_COLUMN_2_NAME, measurement.getIdTrack());
        contentValues.put(DB_MEASUREMENT_COLUMN_3_NAME, measurement.getIdTemperature());
        contentValues.put(DB_MEASUREMENT_COLUMN_4_NAME, measurement.getIdHumidity());
        contentValues.put(DB_MEASUREMENT_COLUMN_5_NAME, measurement.getIdUser());

        nIdRecord = database.insert(SQLLARCTESTHelper.DB_MEASUREMENT_TABLE_NAME, null, contentValues);

        return nIdRecord;
    }

    public long insertMeasurementItem(MeasurementItem measurementItem)
    {
        SimpleDateFormat sdf = new SimpleDateFormat(Constants.FORMAT_DATETIME);

        ContentValues contentValues;
        long nIdRecord;

        contentValues = new ContentValues();

        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_1_NAME, measurementItem.getIdMeasurement());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_2_NAME, measurementItem.getIdHorse());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_3_NAME, measurementItem.getInitTime() == null ? null : sdf.format(measurementItem.getInitTime()));
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_4_NAME, measurementItem.getFinishTime() == null ? null : sdf.format(measurementItem.getFinishTime()));
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_5_NAME, measurementItem.getLacticAcid1());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_6_NAME, measurementItem.getHeartRate1());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_7_NAME, measurementItem.getLacticAcid2());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_8_NAME, measurementItem.getHeartRate2());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_9_NAME, measurementItem.getLacticAcid3());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_10_NAME, measurementItem.getHeartRate3());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_11_NAME, measurementItem.getRespiratoryRate3());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_12_NAME, measurementItem.getIdWork());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_13_NAME, measurementItem.getMeters());
        contentValues.put(DB_MEASUREMENT_ITEM_COLUMN_14_NAME, measurementItem.getTime());

        nIdRecord = database.insert(SQLLARCTESTHelper.DB_MEASUREMENT_ITEM_TABLE_NAME, null, contentValues);

        return nIdRecord;
    }

    public boolean removeMeasurement(Integer idMeasurement)
    {
        //Delete Survey
        database.delete(SQLLARCTESTHelper.DB_MEASUREMENT_ITEM_TABLE_NAME, DB_MEASUREMENT_ITEM_COLUMN_1_NAME + " = " + idMeasurement, null);
        int result = database.delete(SQLLARCTESTHelper.DB_MEASUREMENT_TABLE_NAME, DB_MEASUREMENT_COLUMN_0_NAME + " = " + idMeasurement, null);
        return result > 0;
    }

    public boolean removeMeasurementItems(Integer idMeasurement)
    {
        //Delete Items
        int result = database.delete(SQLLARCTESTHelper.DB_MEASUREMENT_ITEM_TABLE_NAME, DB_MEASUREMENT_ITEM_COLUMN_1_NAME + " = " + idMeasurement, null);
        return result > 0;
    }

    public Measurement getMeasurement(Integer idMeasurement)
    {
        Cursor cursor = null;
        Measurement measurement = null;

        try
        {

            SimpleDateFormat sdf = new SimpleDateFormat(Constants.FORMAT_DATETIME);

            cursor = database.query(SQLLARCTESTHelper.DB_MEASUREMENT_TABLE_NAME, allColumnsMeasurement, DB_MEASUREMENT_COLUMN_0_NAME + " = " + idMeasurement + " AND " + DB_MEASUREMENT_COLUMN_5_NAME + " = " + AppGlobalVariables.getInstance().getIdUser(), null, null, null, null);
            cursor.moveToFirst();

            measurement = new Measurement();
            measurement.setIdMeasurement(cursor.getInt(0));
            measurement.setDateMeasurement(sdf.parse(cursor.getString(1)));
            measurement.setIdTrack(cursor.getInt(2));
            measurement.setIdTemperature(cursor.getInt(3));
            measurement.setIdHumidity(cursor.getInt(4));
            measurement.setIdUser(cursor.getInt(5));
            measurement.setSelected(false);

            measurement.setItems(new ArrayList<MeasurementItem>());

            cursor = database.query(SQLLARCTESTHelper.DB_MEASUREMENT_ITEM_TABLE_NAME, allColumnsMeasurementItems, DB_MEASUREMENT_ITEM_COLUMN_1_NAME + " = " + measurement.getIdMeasurement(), null, null, null, null);
            cursor.moveToFirst();

            while (!cursor.isAfterLast()) {
                MeasurementItem measurementItem = new MeasurementItem();
                measurementItem.setIdMeasurementItem(cursor.getInt(0));
                measurementItem.setIdMeasurement(cursor.getInt(1));
                measurementItem.setIdHorse(cursor.getInt(2));
                measurementItem.setInitTime(cursor.getString(3) == null ? null : sdf.parse(cursor.getString(3)));
                measurementItem.setFinishTime(cursor.getString(4) == null ? null : sdf.parse(cursor.getString(4)));
                measurementItem.setLacticAcid1(cursor.getDouble(5));
                measurementItem.setHeartRate1(cursor.getDouble(6));
                measurementItem.setLacticAcid2(cursor.getDouble(7));
                measurementItem.setHeartRate2(cursor.getDouble(8));
                measurementItem.setLacticAcid3(cursor.getDouble(9));
                measurementItem.setHeartRate3(cursor.getDouble(10));
                measurementItem.setRespiratoryRate3(cursor.getDouble(11));
                measurementItem.setIdWork(cursor.getInt(12));
                measurementItem.setMeters(cursor.getDouble(13));
                measurementItem.setTime(cursor.getDouble(14));

                measurement.getItems().add(measurementItem);

                cursor.moveToNext();
            }

            return measurement;

        }
        catch (Exception e) {
            Log.e(TAG, e.getMessage());
            return measurement;
        }
        finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

    public List<TMeasurementJoin> getMeasurementsJoinItems(String dateTime)
    {
        Cursor cursor = null;
        List<TMeasurementJoin> lstMeasurementsItems = new ArrayList<TMeasurementJoin>();

        try
        {
            SimpleDateFormat sdfTime = new SimpleDateFormat(Constants.FORMAT_DATETIME);

            Date dateFrom = sdfTime.parse(dateTime + " 00:00:00");
            Date dateTo = sdfTime.parse(dateTime + " 24:00:00");

            String strSQL = "SELECT i." + DB_MEASUREMENT_ITEM_COLUMN_1_NAME + ", i." + DB_MEASUREMENT_ITEM_COLUMN_0_NAME + ", h." + DB_MEASUREMENT_COLUMN_HORSE_NAME + ", m." +  DB_MEASUREMENT_COLUMN_1_NAME + " FROM " + SQLLARCTESTHelper.DB_MEASUREMENT_ITEM_TABLE_NAME + " i ";
                    strSQL += " INNER JOIN " + SQLLARCTESTHelper.DB_MEASUREMENT_TABLE_NAME + " m ON i." + DB_MEASUREMENT_ITEM_COLUMN_1_NAME + " = " + " m."+ DB_MEASUREMENT_COLUMN_0_NAME;
                    strSQL += " INNER JOIN " + SQLLARCTESTHelper.DB_HORSES_TABLE_NAME + " h ON i." + DB_MEASUREMENT_ITEM_COLUMN_2_NAME + " = " + " h."+ DB_MEASUREMENT_ITEM_COLUMN_2_NAME;
                    strSQL += " WHERE m." + DB_MEASUREMENT_COLUMN_1_NAME + " BETWEEN '" + sdfTime.format(dateFrom) + "' AND '" + sdfTime.format(dateTo) + "'";
                    strSQL += " AND m."+ DB_MEASUREMENT_COLUMN_5_NAME + " = " + AppGlobalVariables.getInstance().getIdUser();

            cursor = database.rawQuery(strSQL, allJoinColumnsMeasurement);
            cursor.moveToFirst();

            while (!cursor.isAfterLast()) {
                TMeasurementJoin measurementItem = new TMeasurementJoin();

                measurementItem.setIdMeasurement(cursor.getInt(0));
                measurementItem.setIdMeasurementItem(cursor.getInt(1));
                measurementItem.setName(cursor.getString(2));
                measurementItem.setDateMeasurement(cursor.getString(3) == null ? null : sdfTime.parse(cursor.getString(3)));
                measurementItem.setSelected(false);

                lstMeasurementsItems.add(measurementItem);

                cursor.moveToNext();
            }

            return lstMeasurementsItems;

        }
        catch (Exception e) {
            Log.e(TAG, e.getMessage());
            return lstMeasurementsItems;
        }
        finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

    public boolean copyTempMeasurementData()
    {
        TMeasurement tMeasurement;
        List<TMeasurementItem> tLstMeasurementItem;

        try
        {
            dbHelper.beginTransaction();

            MeasurementTempProvider measurementTempProvider = new MeasurementTempProvider(context);

            tMeasurement = measurementTempProvider.getMeasurement();
            tLstMeasurementItem = measurementTempProvider.getMeasurementItems();

            Measurement measurement = new Measurement();
            measurement.setDateMeasurement(new Date());
            measurement.setIdTrack(tMeasurement.getIdTrack());
            measurement.setIdTemperature(tMeasurement.getIdTemperature());
            measurement.setIdHumidity(tMeasurement.getIdHumidity());
            measurement.setIdUser(AppGlobalVariables.getInstance().getIdUser());
            measurement.setSelected(false);

            Integer idMeasurement = (int) insertMeasurement(measurement);

            for(TMeasurementItem item:tLstMeasurementItem) {
                MeasurementItem measurementItem = new MeasurementItem();

                measurementItem.setIdMeasurement(idMeasurement);
                measurementItem.setIdHorse(item.getIdHorse());
                measurementItem.setInitTime(item.getInitTime());
                measurementItem.setFinishTime(item.getFinishTime());
                measurementItem.setLacticAcid1(item.getLacticAcid1());
                measurementItem.setHeartRate1(item.getHeartRate1());
                measurementItem.setLacticAcid2(item.getLacticAcid2());
                measurementItem.setHeartRate2(item.getHeartRate2());
                measurementItem.setLacticAcid3(item.getLacticAcid3());
                measurementItem.setHeartRate3(item.getHeartRate3());
                measurementItem.setRespiratoryRate3(item.getRespiratoryRate3());
                measurementItem.setIdWork(item.getIdWork());
                measurementItem.setMeters(item.getMeters());
                measurementItem.setTime(item.getTime());

                insertMeasurementItem(measurementItem);
            }

            dbHelper.commitTransaction();

            return true;

        }
        catch (Exception e) {
            Log.e(TAG, e.getMessage());
            dbHelper.rollbackTransaction();
            return false;
        }
    }
}
